﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class Expense_BLL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable GetLeafExpense(string bgm_exp_grp_seq)
        {
            try
            {
                string sql = "";
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                if (bgm_exp_grp_seq.Length > 0)
                {
                    sql = "select e.bgm_exp_seq"
                        + ", concat(e2.bgm_exp_name, ' / ', e.bgm_exp_name) as bgm_exp_name"
                        + " from bgm_mas_expense e"
                        + " left join bgm_mas_expense e1 on e1.upper_bgm_exp_seq = e.bgm_exp_seq"
                            + " and e1.record_status not like @RecordStatus"
                        + " left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq"
                            + " and e2.record_status not like @RecordStatus"
                        + " where e.record_status not like @RecordStatus"
                        + " and e.bgm_exp_grp_seq = @bgm_exp_grp_seq"
                        + " and e1.bgm_exp_seq is null";
                }
                else
                {
                    sql = "select e.bgm_exp_seq"
                        + ", concat(g.bgm_exp_grp_name, ' / ', e2.bgm_exp_name, ' / ', e.bgm_exp_name) as bgm_exp_name"
                        + " from bgm_mas_expense e"
                        + " left join bgm_mas_expense e1 on e1.upper_bgm_exp_seq = e.bgm_exp_seq"
                            + " and e1.record_status not like @RecordStatus"
                        + " left join bgm_mas_expense e2 on e.upper_bgm_exp_seq = e2.bgm_exp_seq"
                            + " and e2.record_status not like @RecordStatus"
                        + " left join bgm_mas_expense_group g on e.bgm_exp_grp_seq = g.bgm_exp_grp_seq"
                            + " and g.record_status not like @RecordStatus"
                        + " where e.record_status not like @RecordStatus"
                        + " and e1.bgm_exp_seq is null";
                }

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetExpenseSourceData(string bgm_exp_seq)
        {
            try
            {
                string sql = "";
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_exp_seq", bgm_exp_seq);

                sql = "select bgm_exp_source_seq, bgm_exp_seq, bgm_source_seq"
                    + " from bgm_mas_expense_source"
                    + " where record_status not like @RecordStatus"
                    + " and bgm_exp_seq = @bgm_exp_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetExpenseSourceData(string bgm_exp_seq, string bgm_source_seq)
        {
            try
            {
                string sql = "";
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);

                sql = "select bgm_exp_source_seq, bgm_exp_seq, bgm_source_seq"
                    + " from bgm_mas_expense_source"
                    + " where 0 = 0"
                    + (!string.IsNullOrEmpty(bgm_exp_seq) ? " and bgm_exp_seq = @bgm_exp_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and bgm_source_seq = @bgm_source_seq" : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertExpSource(string bgm_exp_seq, string bgm_source_seq, string emp_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@created_by", emp_seq);
                dbManager.AddParameters(3, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_mas_expense_source"
                    + "(bgm_exp_seq, bgm_source_seq"
                    + ", created_by, created_when)"
                    + " values(@bgm_exp_seq, @bgm_source_seq"
                    + ", @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateExpSource(string bgm_exp_source_seq, string bgm_exp_seq, string bgm_source_seq, string emp_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@bgm_exp_source_seq", bgm_exp_source_seq);
                dbManager.AddParameters(1, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@updated_by", emp_seq);
                dbManager.AddParameters(4, "@updated_when", DateTime.Now);

                string strsql = "update bgm_mas_expense_source"
                    + " set bgm_exp_seq = bgm_exp_seq"
                    + ", bgm_source_seq = @bgm_source_seq"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " where bgm_exp_source_seq = @bgm_exp_source_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteExpSource(string bgm_exp_source_seq, string emp_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_exp_source_seq", bgm_exp_source_seq);
                dbManager.AddParameters(1, "@updated_by", emp_seq);
                dbManager.AddParameters(2, "@updated_when", DateTime.Now);

                string strsql = "update bgm_mas_expense_source"
                    + " set record_status = 'D'"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " where bgm_exp_source_seq = @bgm_exp_source_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void ReturnExpSource(string bgm_exp_source_seq, string emp_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_exp_source_seq", bgm_exp_source_seq);
                dbManager.AddParameters(1, "@updated_by", emp_seq);
                dbManager.AddParameters(2, "@updated_when", DateTime.Now);

                string strsql = "update bgm_mas_expense_source"
                    + " set record_status = 'U'"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " where bgm_exp_source_seq = @bgm_exp_source_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable TreeView()
        {
            try
            {
                string sql = "";
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                sql = "select concat('GROUP-', cast(e.bgm_exp_grp_seq as char)) as bgm_exp_seq"
                    + ", null as upper_bgm_exp_seq"
                    + ", concat(e.bgm_exp_grp_code, ' - ', e.bgm_exp_grp_name) as bgm_exp_name"
                    + " from bgm_mas_expense_group e"
                    + " where e.record_status not like @RecordStatus"

                    + " union all"

                    + " select concat('EXPENSE-', cast(e.bgm_exp_seq as char)) as bgm_exp_seq"
                    + ", case when e.upper_bgm_exp_seq is null"
                        + " then concat('GROUP-', cast(e.bgm_exp_grp_seq as char))"
                        + " else concat('EXPENSE-', cast(e.upper_bgm_exp_seq as char))"
                        + " end as upper_bgm_exp_seq"
                    + ", concat(e.bgm_exp_code, ' - ', e.bgm_exp_name) as bgm_exp_name"
                    + " from bgm_mas_expense e"
                    + " where e.record_status not like @RecordStatus"
                    //+ " and e.bgm_exp_grp_seq = 5"
                    //+ " and (e.upper_bgm_exp_seq = 500000201 or e.upper_bgm_exp_seq is null)"

                    + " order by bgm_exp_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable TreeView(string bgm_exp_grp_seq, string bgm_source_seq)
        {
            try
            {
                string sql = "";
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_exp_grp_seq", bgm_exp_grp_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);

                sql = "select e.bgm_exp_seq"
                    + ", e.upper_bgm_exp_seq"
                    + ", concat(e.bgm_exp_code, ' - ', e.bgm_exp_name) as bgm_exp_name"
                    + " from bgm_mas_expense e"
                    + " inner join bgm_mas_expense_source es on e.bgm_exp_seq = es.bgm_exp_seq"
                        + " and es.record_status not like @RecordStatus"
                    + " where 0 = 0"
                    + " and e.record_status not like @RecordStatus"
                    + (!string.IsNullOrEmpty(bgm_exp_grp_seq) ? " and e.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and es.bgm_source_seq = @bgm_source_seq" : "")

                    + " order by e.upper_bgm_exp_seq, e.bgm_exp_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetInfo(string bgm_exp_seq)
        {
            try
            {
                string sql = "";
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_exp_seq", bgm_exp_seq);

                sql = "select e.*"
                    + ", e1.bgm_exp_seq as bgm_exp_seq1, e1.bgm_exp_name as bgm_exp_name1"
                    + ", e2.bgm_exp_seq as bgm_exp_seq2, e2.bgm_exp_name as bgm_exp_name2"
                    + " from bgm_mas_expense e"
                    + " left join bgm_mas_expense e1 on e.upper_bgm_exp_seq = e1.bgm_exp_seq"
                    + " left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq"
                    + " where e.bgm_exp_seq = @bgm_exp_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetLeafExpenseSourceData(string bgm_source_seq)
        {
            try
            {
                string sql = "";
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);

                sql = "select es.bgm_exp_seq"
                    + " from bgm_mas_expense_source es"
                    //+ " left join bgm_mas_expense e on es.bgm_exp_seq = e.bgm_exp_seq"
                    //+ " left join bgm_mas_expense e1 on e.bgm_exp_seq = e1.upper_bgm_exp_seq"
                    //    + " and e1.record_status not like @delete_status"
                    //+ " where es.record_status not like @delete_status"
                    //+ " and e1.bgm_exp_seq is null"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " where es.bgm_source_seq = @bgm_source_seq" : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
